package com.house.java.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.house.java.dao.SecondHouseDAO;
import com.house.java.model.Paging;
import com.house.java.model.SecondHouse;
import com.house.java.util.DBUtil;

public class SecondHouseDaoImpl implements SecondHouseDAO {
	
	private static Logger log = Logger.getLogger(SecondHouseDAO.class);
	
	public void insertSecondHouse(SecondHouse house) {
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "insert into second_house(" +
			"total_price,house_type,house_area,tel,house_time," +
			"chaoxiang,current_floor,total_floor,zhuangxiu_category,house_category," +
			"build_category,chanquan_quality,location_country_code,location_province_code,location_city_code," +
			"location_district_code,see_time,loupan_name,loupan_location,house_peitao," +
			"images " +
			")" +
			" values(?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?)";
			PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			int parameterIndex = 1;
			pst.setLong(parameterIndex++, house.getTotalPrice());// 总价
			pst.setInt(parameterIndex++, house.getHouseType());// 户型
			pst.setInt(parameterIndex++, house.getHouseArea());// 房屋面积
			pst.setString(parameterIndex++, house.getTelNumber());//  联系方式
			pst.setString(parameterIndex++, house.getHouseTime());// 房子的年代
			
			pst.setString(parameterIndex++, house.getHouseChaoxiang());// 房子的朝向
			pst.setInt(parameterIndex++, house.getFloor());// 房子所在的楼层
			pst.setInt(parameterIndex++, house.getTotalFloor());// 房子所在楼盘的楼层总数
			pst.setInt(parameterIndex++, house.getZhuangxiuCategory());// 装修类型
			pst.setInt(parameterIndex++, house.getHouseCategory());// 房子类别
			
			pst.setInt(parameterIndex++, house.getBuildCategory());// 建筑类别   1、板楼 2、板塔结合 3、多层4、高层
			pst.setInt(parameterIndex++, house.getChanquanQuality());// 房屋产权类别
			pst.setInt(parameterIndex++, house.getLocationCountryCode());// 楼盘所属的国家的CODE
			pst.setInt(parameterIndex++, house.getLocationProvinceCode());// 楼盘所属的省份code
			pst.setInt(parameterIndex++, house.getLocationCityCode());// 楼盘所属的城市code
			
			pst.setInt(parameterIndex++, house.getLocationDistrict());// 楼盘所属的城市里的区code
			pst.setInt(parameterIndex++, house.getSeeTime());// 看房时间
			pst.setString(parameterIndex++, house.getLoupanName());// 楼盘名称
			pst.setString(parameterIndex++, house.getLocation());// 房子所在的楼盘的位置
			pst.setString(parameterIndex++, house.getHousePeitao());//配套设施,如:煤气/天然气/暖气/电梯

			pst.setString(parameterIndex++, house.getImages());// 楼盘图片存储的路径
			
			pst.executeUpdate();
			// 获取刚刚插入的记录的主键，设置到house对象中
			ResultSet resultSet = pst.getGeneratedKeys();
			if(resultSet.next()){
				Long id = resultSet.getLong(1);
				house.setId(id);
			}
			resultSet.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			
			
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		
	}
	
	
	
	private static void logConnectionCloseExceptionFunction(){
		log.error(Thread.currentThread().getId()+" close Connection appear an Exception");
	}
	private static void logConnectionRollbackException(){
		log.error(Thread.currentThread().getId()+" rollback Connection appear an Exception");
	}
	
	/**
	 * 适用于  select count(*) 形式的SQL
	 * @param pst
	 * @param paging
	 */
	private void buildPaging(PreparedStatement pst,Paging paging){
		try {
			ResultSet rs = pst.executeQuery();
			paging.setTotalSize(rs.next()?rs.getInt(1):0);
			log.info(pst.toString());
			rs.close();
			pst.close();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}



	public List<SecondHouse> querySecondHouses(Integer maxPrice,
			Integer minPrice, Paging paging, Integer houseType,
			Integer locationCountryCode, Integer locationProvinceCode,
			Integer locationCityCode, Integer locationDistrict,
			Integer houseCategory) {
		List<SecondHouse> houses = new ArrayList<SecondHouse>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from second_house where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from second_house where 1=1 ");
		int index = 0;
		if(minPrice != null){
			sb.append(" and total_price >= ? ");
			sbCount.append(" and total_price >= ? ");
			index++;
		}
		if(maxPrice != null){
			sb.append(" and total_price <= ? ");
			sbCount.append(" and total_price <= ? ");
			index++;
		}
		if(null != houseType){
			sb.append(" and house_type = ?");
			sbCount.append(" and house_type = ?");
			index++;
		}
		if(null != locationCountryCode){
			sb.append(" and location_country_code = ?");
			sbCount.append(" and location_country_code = ?");
			index++;
		}
		if(null != locationProvinceCode){
			sb.append(" and location_province_code = ?");
			sbCount.append(" and location_province_code = ?");
			index++;
		}
		if(null != locationCityCode){
			sb.append(" and location_city_code = ?");
			sbCount.append(" and location_city_code = ?");
			index++;
		}
		if(null != locationDistrict){
			sb.append(" and location_district_code = ?");
			sbCount.append(" and location_district_code = ?");
			index++;
		}
		if(null != houseCategory){
			sb.append(" and house_category = ? ");
			sbCount.append(" and house_category = ? ");
		}
		
		if(paging != null){
			sb.append(" limit ?,?");
			index++;
			index++;
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(index > 0){
				int paramsIndex = 1;
				if(minPrice!=null){
					pst.setInt(paramsIndex, minPrice);
					pstCount.setInt(paramsIndex, minPrice);
					paramsIndex++;
				}
				if(maxPrice!=null){
					pst.setInt(paramsIndex, maxPrice);
					pstCount.setInt(paramsIndex, maxPrice);
					paramsIndex++;
				}
				if(null != houseType){
					pst.setInt(paramsIndex, houseType);
					pstCount.setInt(paramsIndex, houseType);
					paramsIndex++;
				}
				if(null != locationCountryCode){
					pst.setInt(paramsIndex, locationCountryCode);
					pstCount.setInt(paramsIndex, locationCountryCode);
					paramsIndex++;
				}
				if(null != locationProvinceCode){
					pst.setInt(paramsIndex, locationProvinceCode);
					pstCount.setInt(paramsIndex, locationProvinceCode);
					paramsIndex++;
				}
				if(null != locationCityCode){
					pst.setInt(paramsIndex, locationCityCode);
					pstCount.setInt(paramsIndex, locationCityCode);
					paramsIndex++;
				}
				if(null != locationDistrict){
					pst.setInt(paramsIndex, locationDistrict);
					pstCount.setInt(paramsIndex, locationDistrict);
					paramsIndex++;
				}
				if(null != houseCategory){
					pst.setInt(paramsIndex, houseCategory);
					pstCount.setInt(paramsIndex, houseCategory);
					paramsIndex++;
				}
				if(paging != null){
					pst.setInt(paramsIndex++, (paging.getCurrentPage()-1)*paging.getPageSize());
					pst.setInt(paramsIndex++, paging.getPageSize());
					buildPaging(pstCount, paging);
				}
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			SecondHouse house;
			while(result.next()){
				house = new SecondHouse();
				house.setId(result.getLong("id"));
				house.setLoupanName(result.getString("loupan_name"));
				house.setFloor(result.getInt("current_floor"));
				house.setTotalPrice(result.getLong("total_price"));
				house.setTotalFloor(result.getInt("total_floor"));
				house.setLocation(result.getString("loupan_location"));
				house.setSeeTime(result.getInt("see_time"));
				houses.add(house);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return houses;
	}



	public int delete(Long id) {
		int result = -1;
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "delete from second_house where id = ?";
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setLong(parameterIndex, id);
			// 获取刚刚插入的记录的主键，设置到house对象中
			result = pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		return result;
	}



	public List<SecondHouse> queryById(Long id) {
		List<SecondHouse> houses = new ArrayList<SecondHouse>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from second_house where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from second_house where 1=1 ");
		if(null != id){
			sb.append(" and id=? ");
			sbCount.append(" and id=? ");
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			pst.setLong(1, id);
			pstCount.setLong(1, id);
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			SecondHouse house;
			while(result.next()){
				house = new SecondHouse();
				house.setId(result.getLong("id"));
				house.setLoupanName(result.getString("loupan_name"));
				house.setFloor(result.getInt("current_floor"));
				house.setTotalPrice(result.getLong("total_price"));
				house.setTotalFloor(result.getInt("total_floor"));
				house.setLocation(result.getString("loupan_location"));
				house.setSeeTime(result.getInt("see_time"));
				houses.add(house);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return houses;
	}

}
